﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using MagicBag.CRM.IDAL;
using MagicBag.CRM.Model;
using MagicBag.CRM.Common;


namespace MagicBag.CRM.SqlServerDAL
{

    public class MenuDAL : IMenu
    {
        /// <summary>
        /// 添加菜单
        /// </summary>
        /// <param name="Menu"></param>
        /// <returns></returns>
        public int Insert(Menu menu)
        {
            string sql = "insert into [dbo].[Menu] ([Name] ,[Location] ,[ParentID],[Remark]) values(@Name ,@Location ,@ParentID,@Remark)";
            SqlParameter[] paras = new SqlParameter[4];
            paras[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
            paras[1] = new SqlParameter("@Location", SqlDbType.VarChar, 1000);
            paras[2] = new SqlParameter("@ParentID", SqlDbType.Int);
            paras[3] = new SqlParameter("@Remark", SqlDbType.VarChar, 500);

            paras[0].Value = menu.Name;

            if (string.IsNullOrEmpty(menu.Location))
                paras[1].Value = DBNull.Value;
            else
                paras[1].Value = menu.Location;

            paras[2].Value = menu.ParentID;
            paras[3].Value = menu.Remark;

            using (SqlConnection conn = new SqlConnection(SqlHelper.permissionConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        /// <summary>
        /// 删除菜单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            string sql = "delete from [dbo].[Menu] where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@ID", SqlDbType.Int);
            paras[0].Value = id;
            using (SqlConnection conn = new SqlConnection(SqlHelper.permissionConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        /// <summary>
        /// 修改菜单
        /// </summary>
        /// <param name="Menu"></param>
        /// <returns></returns>
        public int Update(Menu menu)
        {
            string sql = "update [dbo].[Menu] set [Name]= @Name,[Location]=@Location,[ParentID]=@ParentID,[Remark]=@Remark where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
            paras[1] = new SqlParameter("@Location", SqlDbType.VarChar, 1000);
            paras[2] = new SqlParameter("@ParentID", SqlDbType.Int);
            paras[3] = new SqlParameter("@Remark", SqlDbType.VarChar, 500);
            paras[4] = new SqlParameter("@ID", SqlDbType.Int);

            paras[0].Value = menu.Name;
            paras[1].Value = menu.Location;
            paras[2].Value = menu.ParentID;
            paras[3].Value = menu.Remark;
            paras[4].Value = menu.ID;

            using (SqlConnection conn = new SqlConnection(SqlHelper.baseConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        /// <summary>
        /// 根据ID获取菜单
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Menu GetMenu(int id)
        {
            return null;
        }

        /// <summary>
        /// 获取所有菜单
        /// </summary>
        /// <returns></returns>
        public IList<Menu> GetMenus()
        {

            IList<Menu> list = new List<Menu>();

            string sql = "select  [ID] ,[Name] ,[Location] ,[ParentID],[Remark]  FROM [dbo].[Menu]";

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, null))
            {
                while (sdr.Read())
                {
                    Menu menu = new Menu();
                    menu.ID = sdr.GetInt32(0);
                    menu.Name = sdr.GetString(1);
                    menu.Location = sdr.IsDBNull(2) ? null : sdr.GetString(2);
                    menu.ParentID = sdr.IsDBNull(3) ? 0 : sdr.GetInt32(3);
                    menu.Remark = sdr.IsDBNull(4) ? null : sdr.GetString(4);
                    list.Add(menu);
                }
            }
            return list;
        }
    }
}
